import mysql.connector
from mysql.connector import Error
import os
from dotenv import load_dotenv
import logging
from typing import Optional, Dict, Any, List
import json

class DBManager:
    _instance = None
    
    def __new__(cls):
        if cls._instance is None:
            cls._instance = super(DBManager, cls).__new__(cls)
            cls._instance._initialized = False
        return cls._instance
    
    def __init__(self):
        if self._initialized:
            return
            
        self.logger = logging.getLogger(self.__class__.__name__)
        load_dotenv()
        
        self.db_config = {
            "host": os.getenv("DB_HOST", "101.43.226.190"),
            "port": int(os.getenv("DB_PORT", "13306")),
            "user": os.getenv("DB_USER"),
            "password": os.getenv("DB_PASSWORD"),
            "database": os.getenv("DB_NAME")
        }
        
        # 验证配置
        if not all(self.db_config.values()):
            raise ValueError("Missing database configuration. Please check .env file.")
            
        self._initialized = True
        self._terms_cache = {}
        self._models_cache = {}
    
    def get_connection(self):
        """获取数据库连接"""
        try:
            return mysql.connector.connect(**self.db_config)
        except Error as e:
            self.logger.error(f"Database connection failed: {str(e)}")
            raise
    
    def register_model(self, model_name: str, model_type: str, config: Dict) -> bool:
        """注册新模型"""
        try:
            conn = self.get_connection()
            cursor = conn.cursor()
            
            query = """
                INSERT INTO models 
                (model_name, model_type, source_lang, target_lang, provider, model_path, config)
                VALUES (%s, %s, %s, %s, %s, %s, %s)
                ON DUPLICATE KEY UPDATE
                config = VALUES(config),
                updated_at = CURRENT_TIMESTAMP
            """
            
            cursor.execute(query, (
                model_name,
                model_type,
                config.get('source_lang'),
                config.get('target_lang'),
                config.get('provider', 'huggingface'),
                config.get('model_path', model_name),
                json.dumps(config)
            ))
            
            conn.commit()
            cursor.close()
            conn.close()
            
            # 清除缓存
            self._models_cache.clear()
            return True
            
        except Error as e:
            self.logger.error(f"Failed to register model: {str(e)}")
            return False
    
    def get_model_config(self, model_name: str, model_type: str) -> Optional[Dict]:
        """获取模型配置"""
        cache_key = f"{model_type}:{model_name}"
        
        # 检查缓存
        if cache_key in self._models_cache:
            return self._models_cache[cache_key]
        
        try:
            conn = self.get_connection()
            cursor = conn.cursor(dictionary=True)
            
            query = """
                SELECT * FROM models 
                WHERE model_name = %s AND model_type = %s AND is_active = true
                LIMIT 1
            """
            cursor.execute(query, (model_name, model_type))
            result = cursor.fetchone()
            
            cursor.close()
            conn.close()
            
            if result:
                # 解析JSON配置
                if isinstance(result['config'], str):
                    result['config'] = json.loads(result['config'])
                # 更新缓存
                self._models_cache[cache_key] = result
                return result
            
            return None
            
        except Error as e:
            self.logger.error(f"Failed to get model config: {str(e)}")
            return None
    
    def log_model_usage(self, model_id: int, request_type: str, 
                       input_length: int, output_length: int,
                       processing_time: float, status: str,
                       error_message: Optional[str] = None) -> bool:
        """记录模型使用情况"""
        try:
            conn = self.get_connection()
            cursor = conn.cursor()
            
            query = """
                INSERT INTO model_usage 
                (model_id, request_type, input_length, output_length, 
                 processing_time, status, error_message)
                VALUES (%s, %s, %s, %s, %s, %s, %s)
            """
            
            cursor.execute(query, (
                model_id, request_type, input_length, output_length,
                processing_time, status, error_message
            ))
            
            conn.commit()
            cursor.close()
            conn.close()
            
            return True
            
        except Error as e:
            self.logger.error(f"Failed to log model usage: {str(e)}")
            return False
    
    def get_available_models(self, model_type: Optional[str] = None) -> List[Dict]:
        """获取可用的模型列表"""
        try:
            conn = self.get_connection()
            cursor = conn.cursor(dictionary=True)
            
            if model_type:
                query = """
                    SELECT * FROM models 
                    WHERE model_type = %s AND is_active = true
                    ORDER BY created_at DESC
                """
                cursor.execute(query, (model_type,))
            else:
                query = """
                    SELECT * FROM models 
                    WHERE is_active = true
                    ORDER BY model_type, created_at DESC
                """
                cursor.execute(query)
                
            results = cursor.fetchall()
            
            cursor.close()
            conn.close()
            
            # 解析JSON配置
            for result in results:
                if isinstance(result['config'], str):
                    result['config'] = json.loads(result['config'])
            
            return results
            
        except Error as e:
            self.logger.error(f"Failed to get available models: {str(e)}")
            return []
    
    def load_technical_terms(self, source_lang: str, target_lang: str) -> Dict[str, str]:
        """加载技术术语（使用缓存）"""
        cache_key = f"{source_lang}_{target_lang}"
        
        # 如果缓存中存在，直接返回
        if cache_key in self._terms_cache:
            return self._terms_cache[cache_key]
        
        terms = {}
        try:
            conn = self.get_connection()
            cursor = conn.cursor()
            
            query = """
                SELECT source_term, target_term 
                FROM technical_terms 
                WHERE source_lang = %s AND target_lang = %s
            """
            cursor.execute(query, (source_lang, target_lang))
            
            for source_term, target_term in cursor.fetchall():
                terms[source_term] = target_term
                
            cursor.close()
            conn.close()
            
            # 更新缓存
            self._terms_cache[cache_key] = terms
            self.logger.info(f"Loaded {len(terms)} technical terms for {source_lang}->{target_lang}")
            
            return terms
            
        except Error as e:
            self.logger.error(f"Failed to load technical terms: {str(e)}")
            return {}
    
    def clear_cache(self):
        """清除所有缓存"""
        self._terms_cache.clear()
        self._models_cache.clear() 